<?php

/**
 * old actions.
 *
 * @package    sf_sandbox
 * @subpackage home
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 2692 2006-11-15 21:03:55Z fabien $
 */
class goldActions extends sfActions
{
    /**
     * Executes index action
     *
     */
    public function executeIndex()
    {
    }

    public function executeReinvestCps()
    {
        // MAINTENANCE BALANCE (USD) = Once reached USD 500, let them reinvest back
        // (Once reinvested, if the No. of CPS reached 1000/2500/5000/10k/15k CPS, the direct sponsor/pairing bonus % will different too)
        $this->maintenance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_LEDGER_ACTION_MAINTENANCE);

        if ($this->getRequestParameter('transactionPassword') <> "" && $this->getRequestParameter('reinvestAmount') <> "") {
            //$ledgerCpsBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_CPS);
            $ledgerCpsBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ESHARE);
            $ledgerMaintenanceBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MAINTENANCE);
            $reinvestAmount = $this->getRequestParameter('reinvestAmount');

            $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $this->forward404Unless($distDB);

            $distPackage = MlmPackagePeer::retrieveByPK($distDB->getRankId());
            $currentPackageAmount = $distPackage->getPrice();

            $tbl_user = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));

            if ($reinvestAmount > $ledgerMaintenanceBalance) {
                $this->setFlash('errorMsg', "In-sufficient Maintenance balance");
            } else if ($tbl_user->getUserpassword2() <> $this->getRequestParameter('transactionPassword')) {
                $this->setFlash('errorMsg', "Invalid Security password");
            } else {
                /*********************************/
                /*********     CPS     ***********/
                /*********************************/
                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                //$tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_CPS);
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ESHARE);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REINVEST_CPS);
                $tbl_account_ledger->setCredit($reinvestAmount);
                $tbl_account_ledger->setDebit(0);
                $tbl_account_ledger->setBalance($ledgerCpsBalance + $reinvestAmount);
                $tbl_account_ledger->setRemark("");
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                //$this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_CPS);
                $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ESHARE);

                //$ledgerCpsBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_CPS);
                $ledgerCpsBalance = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ESHARE);

                $c = new Criteria();
                $c->add(MlmPackagePeer::CPS, $ledgerCpsBalance, Criteria::LESS_EQUAL);
                $c->addDescendingOrderByColumn(MlmPackagePeer::CPS);
                $reinvestPackage = MlmPackagePeer::doSelectOne($c);

                $packageUpgraded = false;
                if ($reinvestPackage->getPackageId() != $distPackage->getPackageId()) {
                    $packageUpgraded = true;
                }
                /*********************************/
                /********* MAINTENANCE ***********/
                /*********************************/
                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REINVEST_CPS);
                $tbl_account_ledger->setCredit(0);
                $tbl_account_ledger->setDebit($reinvestAmount);
                $tbl_account_ledger->setBalance($ledgerMaintenanceBalance - $reinvestAmount);
                if ($packageUpgraded) {
                    $tbl_account_ledger->setRemark("PACKAGE UPGRADED FROM " . $distPackage->getPackageName() . " => " . $reinvestPackage->getPackageName());

                    $distDB->setRankId($reinvestPackage->getPackageId());
                    $distDB->setRankCode($reinvestPackage->getPackageName());
                    $distDB->save();

                    $c = new Criteria();
                    $c->add(MlmDistPairingPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
                    $distPairingDB = MlmDistPairingPeer::doSelectOne($c);

                    if (!$distPairingDB) {
                        $distPairingDB = new MlmDistPairing();
                        $distPairingDB->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                        $distPairingDB->setLeftBalance(0);
                        $distPairingDB->setRightBalance(0);
                    }
                    $distPairingDB->setFlushLimit($reinvestPackage->getDailyMaxPairing());
                    $distPairingDB->save();

                    /*******************************************/
                    /*********     Trading Point     ***********/
                    /*******************************************/
                    $tradingPoint = ($reinvestPackage->getGoldTradingScale() * $reinvestPackage->getPrice()) - ($distPackage->getGoldTradingScale() * $distPackage->getPrice());

                    $c = new Criteria();
                    $c->add(MlmAccountLedgerPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
                    $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_TRADING_POINT);
                    $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
                    $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);
                    $this->forward404Unless($accountLedgerDB);
                    $distAccountTradingPointBalance = $accountLedgerDB->getBalance();

                    $tbl_account_ledger = new MlmAccountLedger();
                    $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                    $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_TRADING_POINT);
                    $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REINVEST_CPS);
                    $tbl_account_ledger->setCredit($tradingPoint);
                    $tbl_account_ledger->setDebit(0);
                    $tbl_account_ledger->setBalance($distAccountTradingPointBalance + $tradingPoint);
                    $tbl_account_ledger->setRemark("REINVEST CPS:" . $reinvestAmount);
                    $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $tbl_account_ledger->save();

                    $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_TRADING_POINT);

                    /*******************************************/
                    /*********          FOREX        ***********/
                    /*******************************************/
                    // Once new member joined, we give 24 months x gold dividend (500 CPS = 1g, 1g = USD 80), means USD 1,920 in FOREX BALANCE, let them bet on FOREX.
                    $forex = $reinvestAmount / Globals::CPS_PER_GRAM_OF_GOLD * Globals::FOREX_VALUE_PER_GOLD * Globals::FOREX_MONTHS;

                    $c = new Criteria();
                    $c->add(MlmAccountLedgerPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
                    $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_FOREX);
                    $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
                    $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);
                    $this->forward404Unless($accountLedgerDB);
                    $distAccountForexBalance = $accountLedgerDB->getBalance();

                    $tbl_account_ledger = new MlmAccountLedger();
                    $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                    $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_FOREX);
                    $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_REINVEST_CPS);
                    $tbl_account_ledger->setCredit($forex);
                    $tbl_account_ledger->setDebit(0);
                    $tbl_account_ledger->setBalance($distAccountForexBalance + $forex);
                    $tbl_account_ledger->setRemark("REINVEST CPS:" . $reinvestAmount);
                    $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                    $tbl_account_ledger->save();

                    $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_FOREX);
                }
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_MAINTENANCE);

                /* ****************************************************
                 * Update distributor account
                 * ***************************************************/
                $dateUtil = new DateUtil();
                $currentDate = $dateUtil->formatDate("Y-m-d", date("Y-m-d")) . " 00:00:00";
                $dividendDate = $dateUtil->addDate($currentDate, 30, 0, 0);

                $mlm_gold_dividend = new MlmGoldDividend();
                $mlm_gold_dividend->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $mlm_gold_dividend->setIdx(1);
                //$mlm_gold_dividend->setAccountLedgerId($this->getRequestParameter('account_ledger_id'));
                $mlm_gold_dividend->setDividendDate($dividendDate);
                $mlm_gold_dividend->setCps($reinvestAmount);
                //$mlm_gold_dividend->setGoldPrice($this->getRequestParameter('gold_price'));
                //$mlm_gold_dividend->setDividendAmount($this->getRequestParameter('dividend_amount'));
                //$mlm_gold_dividend->setRemarks($this->getRequestParameter('remarks'));
                $mlm_gold_dividend->setStatusCode(Globals::DIVIDEND_STATUS_ACTIVE);
                $mlm_gold_dividend->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_gold_dividend->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $mlm_gold_dividend->save();

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("CPS reinvest successful."));

                return $this->redirect('/gold/reinvestCps');
            }
        }
    }

    public function executeConvertDividendToEcash()
    {
        // ADD “Gold Dividend (USD)”: (No. of CPS) x Current Gold Price (USD/g, round down) x 50%
        // (Click convert gold dividend into e-cash, then gold dividend will be transferred into e-cash wallet)
        $goldDividend = $this->getAccountBalance($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_GOLD);
        $this->goldDividend = $goldDividend;
        if ($this->getRequestParameter('transactionPassword') <> "" && $this->getRequestParameter('dividendAmount') <> "") {
            $dividendAmount = $this->getRequestParameter('dividendAmount');

            $distDB = MlmDistributorPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $this->forward404Unless($distDB);

            $tbl_user = AppUserPeer::retrieveByPk($this->getUser()->getAttribute(Globals::SESSION_USERID));

            if ($dividendAmount > $goldDividend) {
                $this->setFlash('errorMsg', "In-sufficient dividend balance");
            } else if ($tbl_user->getUserpassword2() <> $this->getRequestParameter('transactionPassword')) {
                $this->setFlash('errorMsg', "Invalid Security password");
            } else {
                /**************************************/
                /*********     DIVIDEND     ***********/
                /**************************************/
                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_GOLD);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_CONVERT_ECASH);
                $tbl_account_ledger->setCredit(0);
                $tbl_account_ledger->setDebit($dividendAmount);
                $tbl_account_ledger->setBalance($goldDividend - $dividendAmount);
                $tbl_account_ledger->setRemark("");
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_GOLD);

                /*********************************/
                /************  ECASH *************/
                /*********************************/
                $c = new Criteria();
                $c->add(MlmAccountLedgerPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_ECASH);
                $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
                $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);
                $this->forward404Unless($accountLedgerDB);
                $distAccountEcashBalance = $accountLedgerDB->getBalance();

                $tbl_account_ledger = new MlmAccountLedger();
                $tbl_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
                $tbl_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
                $tbl_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_CONVERT_ECASH);
                $tbl_account_ledger->setCredit($dividendAmount);
                $tbl_account_ledger->setDebit(0);
                $tbl_account_ledger->setBalance($distAccountEcashBalance + $dividendAmount);
                $tbl_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
                $tbl_account_ledger->save();

                $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);

                $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Convert to e-Cash successful."));

                return $this->redirect('/gold/convertDividendToEcash');
            }
        }
    }

    public function executeBuyGold()
    {
        $buyQty = $this->getRequestParameter('txtBuyAmount');

        $goldTradingService = new GoldTradingService();
        $goldTradingDto = $goldTradingService->fetchAllTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $distTradingAmount = $goldTradingDto->getTradingPoint();
        $currentGoldPrice = $goldTradingDto->getCurrentGoldPrice();

        $tradingPointSpend = $currentGoldPrice * $buyQty;

        if ($buyQty == 0) {
            $this->setFlash('successMsg', "Quantity to buy cannot be zero.");
        } else if ($distTradingAmount < $tradingPointSpend) {
            $this->setFlash('successMsg', "In-sufficient Trading Point.");
        } else {
            /******************************/
            /*  Trading Point
            /******************************/
            $c = new Criteria();
            $c->add(MlmAccountLedgerPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_TRADING_POINT);
            $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
            $accountTradingPoingDB = MlmAccountLedgerPeer::doSelectOne($c);

            $tradingPointBalance = 0;
            if ($accountTradingPoingDB)
                $tradingPointBalance = $accountTradingPoingDB->getBalance();

            $mlm_account_ledger = new MlmAccountLedger();
            $mlm_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_TRADING_POINT);
            $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_BUY_EGOLD);
            $mlm_account_ledger->setRemark("BUY GOLD QTY :" . $buyQty . ", PRICE :" . $currentGoldPrice);
            $mlm_account_ledger->setCredit(0);
            $mlm_account_ledger->setDebit($tradingPointSpend);
            $mlm_account_ledger->setBalance($tradingPointBalance - $tradingPointSpend);
            $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_account_ledger->save();

            $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_TRADING_POINT);
            /* ******************************
           *  Update Gold account
           ******************************* */
            $mlm_egold_account = new MlmEgoldAccount();
            $mlm_egold_account->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
            $mlm_egold_account->setBuyGram($buyQty);
            $mlm_egold_account->setBuyPrice($currentGoldPrice);
            //$mlm_egold_account->setSellGram($this->getRequestParameter('sell_gram'));
            //$mlm_egold_account->setSellPrice($this->getRequestParameter('sell_price'));
            $mlm_egold_account->setShareBalance($buyQty);
            $mlm_egold_account->setTradingPoint($tradingPointSpend);
            $mlm_egold_account->setRemark("");
            /*if ($this->getRequestParameter('sell_date')) {
                list($d, $m, $y) = sfI18N::getDateForCulture($this->getRequestParameter('sell_date'), $this->getUser()->getCulture());
                $mlm_egold_account->setSellDate("$y-$m-$d");
            }*/
            $mlm_egold_account->setStatusCode(Globals::EGOLD_ACCOUNT_STATUS_ACTIVE);
            $mlm_egold_account->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_egold_account->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $mlm_egold_account->save();

            $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Buy gold successfully."));

            return $this->redirect('/gold/trading');
        }
    }    

    public function executeSellGold()
    {
        $goldId = $this->getRequestParameter('goldId');
        $currentDate = date("Y-m-d h:i:s A");

        $c = new Criteria();
        $c->add(MlmEgoldAccountPeer::EGOLD_ID, $goldId);
        $c->add(MlmEgoldAccountPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmEgoldAccountPeer::BUY_GRAM, 0, Criteria::NOT_EQUAL);
        $mlm_egold_account = MlmEgoldAccountPeer::doSelectOne($c);
        $this->forward404Unless($mlm_egold_account);

        Globals::SESSION_DISTID; // this is neccessary to initialial Globals class
        $goldTradingService = new GoldTradingService();
        $goldTradingDto = $goldTradingService->fetchAllTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $companyBuyGoldPrice = $goldTradingDto->getCompanyBuyGoldPrice();

        $profit = $this->format2decimal(($companyBuyGoldPrice - $mlm_egold_account->getBuyPrice()) * $mlm_egold_account->getBuyGram());

        $mlm_egold_account->setSellGram($mlm_egold_account->getBuyGram());
        $mlm_egold_account->setSellPrice($companyBuyGoldPrice);
        $mlm_egold_account->setShareBalance(0);
        $mlm_egold_account->setProfit($profit);
        //$mlm_egold_account->setRemark("");
        $mlm_egold_account->setSellDate($currentDate);
        $mlm_egold_account->setStatusCode(Globals::EGOLD_ACCOUNT_STATUS_COMPLETE);
        $mlm_egold_account->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_egold_account->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_egold_account->save();

        $distId = $mlm_egold_account->getDistId();

        /* ******************************
         *  Update Account Trading Point
         ******************************* */
        $c = new Criteria();
        $c->add(MlmAccountLedgerPeer::DIST_ID, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_TRADING_POINT);
        $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
        $accountTradingPoingDB = MlmAccountLedgerPeer::doSelectOne($c);

        $tradingPointBalance = 0;
        if ($accountTradingPoingDB)
            $tradingPointBalance = $accountTradingPoingDB->getBalance();

        $mlm_account_ledger = new MlmAccountLedger();
        $mlm_account_ledger->setDistId($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_TRADING_POINT);
        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_SELL_EGOLD);
        $mlm_account_ledger->setRemark("SELL GOLD QTY :" . $mlm_egold_account->getBuyGram() . ", PRICE :" . $companyBuyGoldPrice.", PROFIT :".$profit);
        $mlm_account_ledger->setCredit($mlm_egold_account->getTradingPoint());
        $mlm_account_ledger->setDebit(0);
        $mlm_account_ledger->setBalance($tradingPointBalance + $mlm_egold_account->getTradingPoint());
        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->save();

        $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_TRADING_POINT);

        /* **********************************
         *  e-cash
         ************************************/
        $ecashCredit = 0;
        $ecashDebit = 0;
        if ($profit >= 0) {
            $ecashCredit = $profit;
        } else {
            $ecashDebit = $profit * -1;
        }
        //$maintenanceBalance = $ecashAfterSell * Globals::BONUS_MAINTENANCE_PERCENTAGE;

        $c = new Criteria();
        $c->add(MlmAccountLedgerPeer::DIST_ID, $distId);
        $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_ECASH);
        $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
        $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);
        $this->forward404Unless($accountLedgerDB);

        $distAccountECashBalance = $accountLedgerDB->getBalance();

        $mlm_account_ledger = new MlmAccountLedger();
        $mlm_account_ledger->setDistId($distId);
        $mlm_account_ledger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
        $mlm_account_ledger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_SELL_EGOLD);
        $mlm_account_ledger->setRemark("SELL GOLD QTY :" . $mlm_egold_account->getBuyGram() . ", PRICE :" . $companyBuyGoldPrice.", PROFIT :".$profit);
        $mlm_account_ledger->setCredit($ecashCredit);
        $mlm_account_ledger->setDebit($ecashDebit);
        $mlm_account_ledger->setBalance($distAccountECashBalance + $profit);
        $mlm_account_ledger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $mlm_account_ledger->save();

        /*$maintenanceEcashAccountLedger = new MlmAccountLedger();
        $maintenanceEcashAccountLedger->setDistId($distId);
        $maintenanceEcashAccountLedger->setAccountType(Globals::ACCOUNT_TYPE_ECASH);
        $maintenanceEcashAccountLedger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_MAINTENANCE);
        $maintenanceEcashAccountLedger->setRemark("MAINTENANCE BALANCE (" . number_format($ecashAfterSell, 2) . ")");
        $maintenanceEcashAccountLedger->setCredit(0);
        $maintenanceEcashAccountLedger->setDebit($maintenanceBalance);
        $maintenanceEcashAccountLedger->setBalance($distAccountECashBalance + $ecashAfterSell - $maintenanceBalance);
        $maintenanceEcashAccountLedger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $maintenanceEcashAccountLedger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $maintenanceEcashAccountLedger->save();*/

        $this->revalidateAccount($this->getUser()->getAttribute(Globals::SESSION_DISTID), Globals::ACCOUNT_TYPE_ECASH);
        /******************************/
        /*  Maintenance
        /******************************/
        /*$c = new Criteria();
        $c->add(MlmAccountLedgerPeer::DIST_ID, $distId);
        $c->add(MlmAccountLedgerPeer::ACCOUNT_TYPE, Globals::ACCOUNT_TYPE_MAINTENANCE);
        $c->addDescendingOrderByColumn(MlmAccountLedgerPeer::CREATED_ON);
        $accountLedgerDB = MlmAccountLedgerPeer::doSelectOne($c);
        $this->forward404Unless($accountLedgerDB);
        $distAccountMaintenanceBalance = $accountLedgerDB->getBalance();

        $maintenanceAccountLedger = new MlmAccountLedger();
        $maintenanceAccountLedger->setDistId($distId);
        $maintenanceAccountLedger->setAccountType(Globals::ACCOUNT_TYPE_MAINTENANCE);
        $maintenanceAccountLedger->setTransactionType(Globals::ACCOUNT_LEDGER_ACTION_SELL_ESHARE);
        $maintenanceAccountLedger->setRemark("SELL ESHARE VOLUME:" . number_format($credit, 2) . ", VALUE:" . $sellPrice . " (" . $currentDate . ")");
        $maintenanceAccountLedger->setCredit($maintenanceBalance);
        $maintenanceAccountLedger->setDebit(0);
        $maintenanceAccountLedger->setBalance($distAccountMaintenanceBalance + $maintenanceBalance);
        $maintenanceAccountLedger->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $maintenanceAccountLedger->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        $maintenanceAccountLedger->save();

        $this->revalidateAccount($distId, Globals::ACCOUNT_TYPE_MAINTENANCE);*/

        $this->setFlash('successMsg', $this->getContext()->getI18N()->__("Sell gold successfully."));
        return $this->redirect('/gold/trading');
    }

    public function executeFetchGoldPrice()
    {
        Globals::SESSION_DISTID; // this is neccessary to initialial Globals class
        $goldTradingService = new GoldTradingService();
        $goldSell = $goldTradingService->fetchGoldSell();
        $goldBuy = $goldTradingService->fetchGoldBuy($goldSell);

        $output = array(
            "goldSell" => $goldSell,
            "goldBuy" => $goldBuy
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeTrading_bak()
    {
        $goldTradingService = new GoldTradingService();
        $this->goldDto = $goldTradingService->fetchAllGoldTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));

        $c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_GOLD_MARKET);
        $this->tbl_setting = AppSettingPeer::doSelectOne($c);

        $c = new Criteria();
        $c->add(TblAccountPeer::F_TYPE, Globals::ACCOUNT_TYPE_GOLD_DIVIDEND);
        $c->addAnd(TblAccountPeer::dist_id, $this->getUser()->getAttribute(Globals::SESSION_DISTID));
        $this->account = TblAccountPeer::doSelectOne($c);
    }

    public function executeTrading()
    {
        /* *****************************************
         * Setting
         * ******************************************/
        $validToBuyGold = false;

        $c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_GOLD_MARKET);
        $settingDB = AppSettingPeer::doSelectOne($c);

        $c = new Criteria();
        $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_GOLD_MARKET_FORCE_CLOSE);
        $setting2DB = AppSettingPeer::doSelectOne($c);

        if ($settingDB->getSettingValue() == "0" && $setting2DB->getSettingValue() == "0") {
            $validToBuyGold = true;
        }
        $this->validToBuyGold = $validToBuyGold;

        $goldTradingService = new GoldTradingService();
        $this->goldTradingDto = $goldTradingService->fetchAllTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));

        //$eshareTradingService = new EShareTradingService();
        //$this->eshareTradingDto = $eshareTradingService->fetchAllShareTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));
        /* *****************************************
         * fetch last 14 days share price log
         * ******************************************/
        $dateUtil = new DateUtil();
        $date = $dateUtil->formatDate("Y-m-d", date("Y-m-d"));
        $query = "SELECT history_id, gold_price, created_by, created_on, updated_by, updated_on
	                    FROM mlm_gold_price_history WHERE created_on >= '" . $date . " 00:00:00' and created_on <= '" . $date . " 23:59:59' order by created_on";
        //var_dump($query);
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $arr = array();
        $i = 0;
        while ($resultset->next()) {
            $arr[$i++] = $resultset->getRow();
        }
        $this->goldPriceLogs = $arr;
    }

    public function executeFetchTradingData()
    {
        $goldTradingService = new GoldTradingService();
        $goldDto = $goldTradingService->fetchAllGoldTradingData($this->getUser()->getAttribute(Globals::SESSION_DISTID));

        $output = array(
            "averagePriceBuy" => $goldDto->getAveragePriceBuy(),
            "goldBuy" => $goldDto->getGoldBuy(),
            "goldSell" => $goldDto->getGoldSell(),
            "paperGoldQuantity" => $goldDto->getPaperGoldQuantity(),
            "tradingMarginAvailable" => $goldDto->getTradingMarginAvailable(),
            "tradingMarginBalance" => $goldDto->getTradingMarginBalance(),
            "unrealizedProfitLoss" => $goldDto->getUnrealizedProfitLoss(),
            "ecash" => $goldDto->getEcash()
        );

        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeOpenTransactionListing()
    {
        Globals::SESSION_DISTID; // this is neccessary to initialial Globals class
        $goldTradingService = new GoldTradingService();
        $goldSell = $goldTradingService->fetchGoldSell();
        $goldBuy = $goldTradingService->fetchGoldBuy();

        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);
        $sColumns = str_replace("marginUsed", "(buy_gram * buy_price) AS marginUsed", $sColumns);
        $sColumns = str_replace("unrealizedProfitLoss", "buy_gram * (" . $goldBuy . " - buy_price) AS unrealizedProfitLoss", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        $sql = "FROM mlm_egold_account";

        /******   total records  *******/
        $sWhere = " WHERE dist_id =" . $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $sWhere .= " AND buy_gram > 0 AND sell_gram = 0 ";
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;

        //var_dump($query);
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            //var_dump(round(resultArr['marginUsed']));
            //var_dump("==>".round($resultArr['marginUsed'], 2, PHP_ROUND_HALF_UP));
            $marginUsed = $resultArr['marginUsed'] == null ? 0 : $resultArr['marginUsed'];
            $marginUsed = $this->format2decimal($marginUsed);
            $unrealizedProfitLoss = $resultArr['unrealizedProfitLoss'] == null ? 0 : $resultArr['unrealizedProfitLoss'];
            //$unrealizedProfitLoss = $this->format2decimal($unrealizedProfitLoss);
            $arr[] = array(
                $resultArr['egold_id'] == null ? "" : $resultArr['egold_id'],
                $resultArr['egold_id'] == null ? "" : $resultArr['egold_id'],
                $resultArr['buy_price'] == null ? "0.00" : $resultArr['buy_price'],
                $resultArr['buy_gram'] == null ? "0" : $resultArr['buy_gram'],
                number_format($marginUsed, 2),
                $resultArr['created_on'] == null ? "" : $resultArr['created_on'],
                $unrealizedProfitLoss,
                $resultArr['egold_id'] == null ? "" : $resultArr['egold_id']
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeBuyingHistoryListing()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);
        $sColumns = str_replace("marginUsed", "(buy_gram * buy_price) AS marginUsed", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        $sql = "FROM mlm_egold_account";

        /******   total records  *******/
        $sWhere = " WHERE dist_id =" . $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $sWhere .= " AND buy_gram > 0 ";
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            //var_dump(round(resultArr['marginUsed']));
            //var_dump("==>".round($resultArr['marginUsed'], 2, PHP_ROUND_HALF_UP));
            $marginUsed = $resultArr['marginUsed'] == null ? 0 : $resultArr['marginUsed'];
            $marginUsed = $this->format2decimal($marginUsed);
            $arr[] = array(
                $resultArr['egold_id'] == null ? "" : $resultArr['egold_id'],
                $resultArr['egold_id'] == null ? "" : $resultArr['egold_id'],
                $resultArr['buy_gram'] == null ? "0" : $resultArr['buy_gram'],
                $resultArr['buy_price'] == null ? "0.00" : $resultArr['buy_price'],
                number_format($marginUsed, 2),
                $resultArr['created_on'] == null ? "" : $resultArr['created_on']
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeSellingHistoryListing()
    {
        $sColumns = $this->getRequestParameter('sColumns');
        $aColumns = explode(",", $sColumns);
        $sColumns = str_replace("marginUsed", "(sell_gram * sell_price) AS marginUsed", $sColumns);

        $iColumns = $this->getRequestParameter('iColumns');

        $offset = $this->getRequestParameter('iDisplayStart');
        $sEcho = $this->getRequestParameter('sEcho');
        $limit = $this->getRequestParameter('iDisplayLength');
        $arr = array();

        $sql = "FROM mlm_egold_account";

        /******   total records  *******/
        $sWhere = " WHERE dist_id =" . $this->getUser()->getAttribute(Globals::SESSION_DISTID);
        $sWhere .= " AND share_balance = 0 AND status_code ='".Globals::EGOLD_ACCOUNT_STATUS_COMPLETE."'";
        $totalRecords = $this->getTotalRecords($sql . $sWhere);

        /******   total filtered records  *******/
        $totalFilteredRecords = $this->getTotalRecords($sql . $sWhere);

        /******   sorting  *******/
        $sOrder = "ORDER BY  ";
        for ($i = 0; $i < intval($this->getRequestParameter('iSortingCols')); $i++)
        {
            if ($this->getRequestParameter('bSortable_' . intval($this->getRequestParameter('iSortCol_' . $i))) == "true") {
                $sOrder .= $aColumns[intval($this->getRequestParameter('iSortCol_' . $i))] . "
                    " . mysql_real_escape_string($this->getRequestParameter('sSortDir_' . $i)) . ", ";
            }
        }

        $sOrder = substr_replace($sOrder, "", -2);
        if ($sOrder == "ORDER BY") {
            $sOrder = "";
        }
        //var_dump($sOrder);
        /******   pagination  *******/
        $sLimit = " LIMIT " . mysql_real_escape_string($offset) . ", " . mysql_real_escape_string($limit);

        $query = "SELECT " . $sColumns . " " . $sql . " " . $sWhere . " " . $sOrder . " " . $sLimit;
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();
        while ($resultset->next())
        {
            $resultArr = $resultset->getRow();
            //var_dump(round(resultArr['marginUsed']));
            //var_dump("==>".round($resultArr['marginUsed'], 2, PHP_ROUND_HALF_UP));
            $marginUsed = $resultArr['marginUsed'] == null ? 0 : $resultArr['marginUsed'];
            $marginUsed = $this->format2decimal($marginUsed);
            $arr[] = array(
                $resultArr['egold_id'] == null ? "" : $resultArr['egold_id'],
                $resultArr['egold_id'] == null ? "" : $resultArr['egold_id'],
                $resultArr['sell_gram'] == null ? "0" : $resultArr['sell_gram'],
                $resultArr['sell_price'] == null ? "0.00" : $resultArr['sell_price'],
                number_format($marginUsed, 2),
                $resultArr['profit'] == null ? "0.00" : $resultArr['profit'],
                $resultArr['sell_date'] == null ? "" : $resultArr['sell_date']
            );
        }
        $output = array(
            "sEcho" => intval($sEcho),
            "iTotalRecords" => $totalRecords,
            "iTotalDisplayRecords" => $totalFilteredRecords,
            "aaData" => $arr
        );
        echo json_encode($output);

        return sfView::HEADER_ONLY;
    }

    public function executeUpdateGoldPrice()
    {
        $goldPricePerKg = $this->getRequestParameter('g');

        if (isset($goldPricePerKg) && trim($goldPricePerKg) != '' && $goldPricePerKg > 1000) {
            /*$goldPrice = Globals::GRAM_TO_TROY_OUNCES * $goldPricePerKg;
            $goldPrice = number_format($goldPrice, 4);

            $mlm_gold_price_history = new MlmGoldPriceHistory();
            $mlm_gold_price_history->setGoldPrice($goldPrice);
            $mlm_gold_price_history->save();*/

            /*$a = 15.61;
            $b = 15.61;
            if (abs($a-$b) < 0.00001) {
                echo 'Everything is fine 6666455 =)';
            } else {
                echo 'Okay something is wrong :S';
                var_dump($v); // float 15.6
            }*/

            /*$query = "SELECT history_id, gold_price, created_on
                FROM mlm_gold_price_history order by created_on desc limit 30 ";
            $connection = Propel::getConnection();
            $statement = $connection->prepareStatement($query);
            $resultset = $statement->executeQuery();
            $notMatch = true;
            while ($resultset->next()) {
                $arr = $resultset->getRow();
                if ($goldPrice !== $arr['gold_price']) {
                    $notMatch = false;
                    break;
                }
            }

            $c = new Criteria();
            $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_GOLD_MARKET);
            $tblSettingDB = AppSettingPeer::doSelectOne($c);
            if ($notMatch) {
                $tblSettingDB->setSettingValue("1");
            } else {
                $tblSettingDB->setSettingValue("0");
            }
            $tblSettingDB->save();*/

            echo "SUCCESS";
        } else {
            echo "FAILURE";
        }

        return sfView::HEADER_ONLY;
    }

    public function executeUpdateGoldPrice_BAK()
    {
        $url = "http://www.goldalert.com";
        /* need to enable extension=curl.so in php.ini
         * $crl = curl_init();
        $timeout = 5;
        curl_setopt ($crl, CURLOPT_URL,$url);
        curl_setopt ($crl, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt ($crl, CURLOPT_CONNECTTIMEOUT, $timeout);
        $ret = curl_exec($crl);
        curl_close($crl);*/
        $str = file_get_contents($url);
        $pos = strpos($str, '<div id="gp"><span>$</span>');
        $str = substr($str, $pos + 27, 13);
        $pos = strpos($str, '</div>');
        $goldPricePerKg = substr($str, 0, -($pos - 1));

        if (isset($goldPricePerKg) && trim($goldPricePerKg) != '' && $goldPricePerKg > 1000) {
            $goldPrice = Globals::GRAM_TO_TROY_OUNCES * $goldPricePerKg;
            $goldPrice = number_format($goldPrice, 4);

            $c = new Criteria();
            $c->add(AppSettingPeer::SETTING_PARAMETER, Globals::SETTING_GOLD_PRICE);
            $tblSettingDB = AppSettingPeer::doSelectOne($c);
            if ($tblSettingDB) {
                $mlm_gold_price_history = new MlmGoldPriceHistory();
                $mlm_gold_price_history->setGoldPrice($goldPrice);
                $mlm_gold_price_history->save();

                $tblSettingDB->setSettingValue($goldPrice);
                $tblSettingDB->save();
            }
            echo "SUCCESS";
        } else {
            echo "FAILURE";
        }
        return sfView::HEADER_ONLY;
    }

    public function executeGoldChart()
    {

    }

    /************************************/
    /********   FUNCTION        *********/
    /************************************/
    function getTotalRecords($sql)
    {
        $query = "SELECT COUNT(*) AS _TOTAL " . $sql;
        //var_dump($query);
        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        $count = 0;
        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["_TOTAL"] != null) {
                $count = $arr["_TOTAL"];
            } else {
                $count = 0;
            }
        }
        return $count;
    }

    function format2decimal($d)
    {
        return ceil($d * 100) / 100;
    }

    function getAccountBalance($distributorId, $accountType)
    {
        $query = "SELECT SUM(credit-debit) AS SUB_TOTAL FROM mlm_account_ledger WHERE dist_id = " . $distributorId . " AND account_type = '" . $accountType . "'";

        $connection = Propel::getConnection();
        $statement = $connection->prepareStatement($query);
        $resultset = $statement->executeQuery();

        if ($resultset->next()) {
            $arr = $resultset->getRow();
            if ($arr["SUB_TOTAL"] != null) {
                return $arr["SUB_TOTAL"];
            } else {
                return 0;
            }
        }
        return 0;
    }

    function revalidateAccount($distributorId, $accountType)
    {
        $balance = $this->getAccountBalance($distributorId, $accountType);

        $c = new Criteria();
        $c->add(MlmAccountPeer::ACCOUNT_TYPE, $accountType);
        $c->add(MlmAccountPeer::DIST_ID, $distributorId);
        $tbl_account = MlmAccountPeer::doSelectOne($c);

        if (!$tbl_account) {
            $tbl_account = new MlmAccount();
            $tbl_account->setDistId($distributorId);
            $tbl_account->setAccountType($accountType);
            $tbl_account->setCreatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
            $tbl_account->setUpdatedBy($this->getUser()->getAttribute(Globals::SESSION_USERID, Globals::SYSTEM_USER_ID));
        }

        $tbl_account->setBalance($balance);
        $tbl_account->save();
    }
}